Global Internet Business Solutions ~ GIBS

SQL Function to Lookup DNN Profile Values

Mon, 01 Feb 2021 20:20:00 GMT

CREATE FUNCTION GIBS_GetProfileElement ( @userID as int, @portalID as int, @ProfilePropertyName as nvarchar(100) ) RETURNS nvarchar(4000) AS BEGIN -- If input is invalid, return null. IF @ProfilePropertyName IS NULL OR LEN(@ProfilePropertyName) = 0 OR @userID IS NULL OR @userID < 1 RETURN NULL DECLARE @PropertyValue AS NVARCHAR(400) SET @PropertyValue = ( SELECT UserProfile.PropertyValue FROM (Users INNER JOIN UserProfile ON Users.UserID = UserProfile.UserID) INNER JOIN ProfilePropertyDefinition ON UserProfile.PropertyDefinitionID = ProfilePropertyDefinition.PropertyDefinitionID WHERE (((Users.UserID)=@userID) AND ((ProfilePropertyDefinition.PropertyName)=@ProfilePropertyName) AND ProfilePropertyDefinition.PortalID = @portalID) ) RETURN @PropertyValue END

Pivot Query for Ventrian Property Agent

Mon, 15 Dec 2014 13:22:00 GMT

I had a need for getting a property details for a SSR report and came up with the following store procedure . . . I thought it might be useful to someone. /****** Object: StoredProcedure [dbo].[GIBS_Rentals_Get_PropertyDetails] Script Date: 12/15/2014 06:52:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[GIBS_Rentals_Get_PropertyDetails] ( @PropertyID int ) AS BEGIN SET NOCOUNT ON; DECLARE @cols AS VARCHAR(MAX), @query AS VARCHAR(MAX), @PropertyIDVarchar AS VARCHAR(10) Set @PropertyIDVarchar = Convert(VARCHAR(10),@PropertyID) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(name) FROM Ventrian_PropertyAgent_CustomField FOR XML PATH(''), TYPE ).value('.', 'VARCHAR(MAX)') ,1,1,'') --print @cols set @query = 'SELECT PropertyID,' + @cols + ' from ( SELECT PropertyID , cast(Ventrian_PropertyAgent_PropertyValue.CustomValue as Varchar(50)) AS CustomValue, Ventrian_PropertyAgent_CustomField.Name AS [Name] FROM Ventrian_PropertyAgent_PropertyValue INNER JOIN Ventrian_PropertyAgent_CustomField ON Ventrian_PropertyAgent_PropertyValue.CustomFieldID = Ventrian_PropertyAgent_CustomField.CustomFieldID WHERE Ventrian_PropertyAgent_PropertyValue.PropertyID = ' + @PropertyIDVarchar + ' ) x pivot ( min(CustomValue) for Name in (' + @cols + ') ) p ' --print @query execute(@query) END

Replace Null or Empty Value with String

Tue, 16 Sep 2014 10:28:00 GMT

Select ISNULL(NULLIF(GIBS_FBLineItems.ReportType, ''), '--Undefined--') as ReportType from GIBS_FBLineItems

Finding the Port of MS SQL Named Instance

Thu, 17 Mar 2011 10:12:00 GMT

When running a named instance of MS SQL the instance will run on a port other than the usual port 1433. To determine the port you can use the following query: DECLARE @tcp_port nvarchar(5) EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\MSSQL\MSSQLSERVER\SUPERSOCKETNETLIB\TCP', @value_name = 'TcpPort', @value = @tcp_port OUTPUT select @tcp_port In my case the instance was named "MSSQL".

Deleting a DNN User Account

Wed, 27 Oct 2010 18:32:00 GMT

@rUser table (UserId int) 'Set UserID' into @rUser (UserId) values (9) Print '==================================' Print 'DELETE from aspnet_Membership' DELETE from aspnet_Membership where UserId in (SELECT au.UserId from aspnet_Users au join users u on au.username = u.username where u.userid in (select * from @rUser) ) Print '==================================' Print 'DELETE from aspnet_Users' DELETE from aspnet_Users where UserId in (SELECT au.UserId from aspnet_Users au join users u on au.username = u.username where u.userid in (select * from @rUser) ) -- clear profile properties Print '==================================' Print 'DELETE from UserProfile' DELETE FROM UserProfile where UserID in (SELECT UserId from users where userid in (select * from @rUser) ) -- clear portal relationships with users Print '==================================' Print 'DELETE from UserPortals' DELETE FROM UserPortals where UserID in (SELECT UserId from users where userid in (select * from @rUser) ) -- clear role relationships with users Print '==================================' Print 'DELETE from UserRoles' DELETE FROM UserRoles where UserID in (SELECT UserId from users where userid in (select * from @rUser) ) -- finally remove core user profile info Print '==================================' Print 'DELETE from Users' DELETE FROM users where userid in (select * from @rUser) Print -- WAIT WAIT WAIT WAIT - remember to put in the right userid insert declare

Check for Null Value in a Stored Procedure

Wed, 21 Apr 2010 08:54:00 GMT

Within a stored procedure use an IF STATEMENT to check a parameter for a NULL VALUE if( @CustomFieldID is null) begin print 'NULL VALUE - SKIP IT' end else begin print 'PARAMETER HAS VALUE' end